#!/usr/bin/env python
# coding=utf-8

# 多个工作表合并成一个,格式一致

from pathlib import Path
import xlwings as xw
import os

getpath = os.getcwd()
folder_path = Path(getpath + '\\gzb')
file_list = folder_path.glob('*.xlsx')  # 获取文件夹下所有工作簿的文件路径

app = xw.App(visible=False, add_book=False)
sheet_name = '1月'
header = None
all_data = []
for i in file_list:
    #print(i.name)
    # if i.name.startswith('~$'):
    #     continue
    workbook = app.books.open(i)
    for j in workbook.sheets:
        if j.name == sheet_name:
            if header is None:
                header = j['A1:C1'].value
            data = j['A2'].expand('table').value
            all_data = all_data + data
    workbook.close()

new_workbook = xw.Book()
new_worksheet = new_workbook.sheets.add(sheet_name)
new_worksheet['A1'].value = header
new_worksheet['A2'].value = all_data
new_worksheet.autofit()
new_workbook.save(folder_path / 'x.xlsx')
new_workbook.close()
app.quit()
